﻿Imports System.Data.SqlServerCe

Friend Class SaveCommand(Of PrimaryKeyType)
    Private mTableName As String
    Private mParams As New Generic.List(Of Param)
    Private mIdentityColName As String
    Private mIdentityValue As PrimaryKeyType
    Private mIdentityNullValue As PrimaryKeyType
    Private mIsInsertEx As Boolean = False
    Private mIsIdentity As Boolean = False

    Public Sub New(ByVal tableName As String, ByVal identityColName As String, ByVal identityValue As PrimaryKeyType, ByVal identityNullValue As PrimaryKeyType)
        mTableName = tableName
        mIdentityNullValue = identityNullValue
        mIdentityColName = String.Format("[{0}]", identityColName)
        mIdentityValue = identityValue
        mIsInsertEx = mIdentityValue.Equals(mIdentityNullValue)
        mIsIdentity = True
    End Sub

    Public Sub New(ByVal tableName As String, ByVal primaryKeyColName As String, ByVal primaryKeyValue As PrimaryKeyType)
        mTableName = tableName
        mIdentityColName = String.Format("[{0}]", primaryKeyColName)
        mIdentityValue = primaryKeyValue
    End Sub

    Public ReadOnly Property IsInsert() As Boolean
        Get
            Return mIsInsertEx
        End Get
    End Property

    Public Sub AddParam(ByVal name As String, ByVal value As Object, ByVal nullable As Boolean)
        Dim clsParam As New Param
        clsParam.ColName = String.Format("[{0}]", name)
        clsParam.VariableName = String.Format("@{0}", name)

        If value Is Nothing And Not nullable Then
            Throw New ArgumentNullException("Value cannot be null if column is not nullable!")
        ElseIf value Is Nothing Then
            clsParam.Value = DBNull.Value
        ElseIf TypeOf value Is Integer AndAlso CInt(value) = 0 Then
            clsParam.Value = DBNull.Value
        ElseIf TypeOf value Is Date AndAlso CDate(value) = DateTime.MinValue Then
            clsParam.Value = DBNull.Value
        Else
            clsParam.Value = value
        End If
        mParams.Add(clsParam)
    End Sub

    Public Function Save() As PrimaryKeyType
        Dim oRet As PrimaryKeyType
        Using t As New TransactionScope
            oret = Save(t)
            t.Complete()
        End Using
        Return oRet
    End Function

    Public Function Save(ByVal transactionScope As TransactionScope) As PrimaryKeyType
        If mIsIdentity Then
            Dim sqlCmd As SqlCeCommand = GenerateSqlCommand(mIsIdentity, mIsInsertEx, transactionScope)

            Dim iRowsAffected As Integer = sqlCmd.ExecuteNonQuery

            Select Case iRowsAffected
                Case Is < 1
                    Throw New ApplicationException("No row was affected by id-based update!")
                Case Is > 1
                    Throw New ApplicationException(String.Format("Expected just one row to be affected but in fact {0} were affected!!", iRowsAffected))
            End Select

            If mIsInsertEx Then
                Dim sqlCmd2 As New SqlCeCommand("SELECT @@Identity", transactionScope.Connection)
                sqlCmd2.Transaction = transactionScope.Transaction
                Using dr As SqlCeDataReader = sqlCmd2.ExecuteReader
                    If Not dr.Read Then Throw New ApplicationException("Could not get the identity of newly inserted row!")
                    mIdentityValue = CType(dr.GetValue(0), PrimaryKeyType)
                End Using
            End If
            Return mIdentityValue
        Else
            Dim sqlCmd As SqlCeCommand = GenerateSqlCommand(mIsIdentity, False, transactionScope)

            Dim iRowsAffected As Integer = sqlCmd.ExecuteNonQuery

            Select Case iRowsAffected
                Case 1
                    Return mIdentityValue
                Case Is > 1
                    Throw New ApplicationException(String.Format("Expected just one row to be affected but in fact {0} were affected!!", iRowsAffected))
                Case 0
                    'now, we need to do an insert
            End Select

            sqlCmd = GenerateSqlCommand(mIsIdentity, True, transactionScope)

            iRowsAffected = sqlCmd.ExecuteNonQuery

            Return mIdentityValue
        End If
    End Function

    Private Function GenerateSqlCommand(ByVal isIdentity As Boolean, ByVal generateInsert As Boolean, ByVal transactionScope As TransactionScope) As SqlCeCommand
        Dim clsSB As New System.Text.StringBuilder
        If generateInsert Then
            clsSB.Append("INSERT INTO ")
        Else
            clsSB.Append("UPDATE ")
        End If
        clsSB.Append(mTableName)
        If generateInsert Then
            clsSB.Append(" (")
            For i As Integer = 0 To mParams.Count - 1
                If i > 0 Then clsSB.Append(","c)
                With mParams(i)
                    clsSB.Append(.ColName)
                End With
            Next
            If Not isIdentity Then
                clsSB.Append(","c)
                clsSB.Append(mIdentityColName)
            End If
            clsSB.Append(") VALUES (")
            For i As Integer = 0 To mParams.Count - 1
                If i > 0 Then clsSB.Append(","c)
                With mParams(i)
                    clsSB.Append(.VariableName)
                End With
            Next
            If Not isIdentity Then
                clsSB.Append(",@PrimKey")
            End If
            clsSB.Append(")"c)
        Else
            clsSB.Append(" SET ")
            For i As Integer = 0 To mParams.Count - 1
                If i > 0 Then clsSB.Append(", ")
                With mParams(i)
                    clsSB.Append(.ColName)
                    clsSB.Append("="c)
                    clsSB.Append(.VariableName)
                End With
            Next
            clsSB.Append(" WHERE ")
            clsSB.Append(mIdentityColName)
            clsSB.Append("="c)
            clsSB.Append(mIdentityValue)
        End If

        Dim sqlCmd As SqlCeCommand = Environment.NewCommand(clsSB.ToString, transactionScope)
        For Each clsParam As Param In mParams
            sqlCmd.Parameters.AddWithValue(clsParam.VariableName, clsParam.Value)
        Next
        If generateInsert And Not isIdentity Then
            sqlCmd.Parameters.AddWithValue("@PrimKey", mIdentityValue)
        End If

        Return sqlCmd
    End Function

    Private Class Param
        Public ColName As String
        Public VariableName As String
        Public Value As Object
    End Class
End Class
